#!/usr/bin/env python3
"""
Directory Data Updater for UmaInsight
Updates directory tables when metrics are added or modified
"""

import sqlite3
import sys
import os
from datetime import datetime
from typing import List, Dict, Set

def get_database_path() -> str:
    """Get the path to the SQLite database"""
    current_dir = os.path.dirname(os.path.abspath(__file__))
    backend_data_dir = os.path.join(current_dir, '..', 'backend', 'data')
    db_path = os.path.join(backend_data_dir, 'uma_insight.db')
    return db_path

def extract_directory_from_name(metric_name: str) -> str:
    """Extract directory from metric name"""
    if '.' in metric_name:
        return metric_name.split('.')[0]
    return 'Root'

def sync_directories_with_metrics(cursor, connection):
    """Sync directories table with current metrics data"""
    print("正在同步directories数据...")

    # Get current directories from metrics
    cursor.execute("""
        SELECT DISTINCT
            CASE
                WHEN INSTR(name, '.') > 0
                THEN SUBSTR(name, 1, INSTR(name, '.') - 1)
                ELSE 'Root'
            END as directory
        FROM metrics
        WHERE name IS NOT NULL AND name != ''
    """)

    current_dirs = {row[0] for row in cursor.fetchall()}

    # Get existing directories in database
    cursor.execute("SELECT name FROM directories")
    existing_dirs = {row[0] for row in cursor.fetchall()}

    # Find new directories to add
    new_dirs = current_dirs - existing_dirs
    if new_dirs:
        print(f"发现 {len(new_dirs)} 个新directories，正在添加...")
        for dir_name in new_dirs:
            cursor.execute("INSERT INTO directories (name) VALUES (?)", (dir_name,))
        connection.commit()
        print(f"成功添加 {len(new_dirs)} 个新directories")

    # Find obsolete directories to potentially clean up (keep for now)
    obsolete_dirs = existing_dirs - current_dirs
    if obsolete_dirs:
        print(f"发现 {len(obsolete_dirs)} 个可能过时的directories: {list(obsolete_dirs)[:5]}...")
        print("保留这些directories以防有引用关系")

def update_metric_directory_associations(cursor, connection):
    """Update metric-directories associations"""
    print("正在更新metric-directory关联...")

    # Clear existing associations
    cursor.execute("DELETE FROM metric_directories")

    # Get directory mapping
    cursor.execute("SELECT id, name FROM directories")
    directory_map = {row[1]: row[0] for row in cursor.fetchall()}

    # Get all metrics with directories
    cursor.execute("""
        SELECT id,
               CASE
                   WHEN INSTR(name, '.') > 0
                   THEN SUBSTR(name, 1, INSTR(name, '.') - 1)
                   ELSE 'Root'
               END as directory
        FROM metrics
        WHERE name IS NOT NULL AND name != ''
    """)

    metrics_with_dirs = cursor.fetchall()

    # Prepare associations
    associations = []
    for metric_id, directory in metrics_with_dirs:
        if directory in directory_map:
            directory_id = directory_map[directory]
            associations.append((metric_id, directory_id))

    # Batch insert
    if associations:
        cursor.executemany("""
            INSERT INTO metric_directories (metric_id, directory_id)
            VALUES (?, ?)
        """, associations)

    connection.commit()
    print(f"成功更新 {len(associations)} 条metric-directory关联")

def update_directory_metrics_counts(cursor, connection):
    """Update metric counts for each directory"""
    print("正在更新directory metrics统计...")

    cursor.execute("""
        UPDATE directories
        SET metric_count = (
            SELECT COUNT(*)
            FROM metric_directories md
            WHERE md.directory_id = directories.id
        ),
        updated_at = CURRENT_TIMESTAMP
    """)

    connection.commit()
    print("Directory metrics统计更新完成")

def get_directory_stats(cursor) -> Dict:
    """Get directory statistics"""
    cursor.execute("SELECT COUNT(*) FROM directories WHERE metric_count > 0")
    active_dirs = cursor.fetchone()[0]

    cursor.execute("SELECT SUM(metric_count) FROM directories")
    total_metrics_assigned = cursor.fetchone()[0]

    cursor.execute("SELECT COUNT(*) FROM metric_directories")
    total_associations = cursor.fetchone()[0]

    return {
        'active_directories': active_dirs,
        'total_metrics_assigned': total_metrics_assigned,
        'total_associations': total_associations
    }

def show_directory_report(cursor):
    """Show directory statistics report"""
    print("\n=== Directory统计报告 ===")

    stats = get_directory_stats(cursor)
    print(f"活跃Directory数量: {stats['active_directories']}")
    print(f"已分配Metrics总数: {stats['total_metrics_assigned']}")
    print(f"关联记录总数: {stats['total_associations']}")

    # Show top directories
    cursor.execute("""
        SELECT name, metric_count
        FROM directories
        WHERE metric_count > 0
        ORDER BY metric_count DESC
        LIMIT 15
    """)

    top_dirs = cursor.fetchall()
    print(f"\n📊 Top 15 Directory:")
    for i, (name, count) in enumerate(top_dirs, 1):
        print(f"   {i:2d}. {name:<30} {count:>6} metrics")

def verify_consistency(cursor):
    """Verify data consistency"""
    print("\n正在验证数据一致性...")

    # Check if all metric associations have valid directories
    cursor.execute("""
        SELECT COUNT(*) FROM metric_directories md
        LEFT JOIN directories d ON md.directory_id = d.id
        WHERE d.id IS NULL
    """)
    orphaned_associations = cursor.fetchone()[0]

    if orphaned_associations > 0:
        print(f"⚠️  发现 {orphaned_associations} 条无效的directory关联")
        return False

    # Check if all metric associations have valid metrics
    cursor.execute("""
        SELECT COUNT(*) FROM metric_directories md
        LEFT JOIN metrics m ON md.metric_id = m.id
        WHERE m.id IS NULL
    """)
    orphaned_metrics = cursor.fetchone()[0]

    if orphaned_metrics > 0:
        print(f"⚠️  发现 {orphaned_metrics} 条无效的metric关联")
        return False

    print("✅ 数据一致性验证通过")
    return True

def main():
    """Main function to update directory data"""
    try:
        print("=== UmaInsight Directory数据更新工具 ===")
        print(f"开始时间: {datetime.now()}")

        db_path = get_database_path()
        print(f"连接数据库: {db_path}")

        connection = sqlite3.connect(db_path)
        cursor = connection.cursor()

        # Check if directories table exists
        cursor.execute("""
            SELECT name FROM sqlite_master
            WHERE type='table' AND name='directories'
        """)
        if not cursor.fetchone():
            print("❌ directories表不存在，请先运行 create_directory_tables.py")
            sys.exit(1)

        # Sync directories with current metrics
        sync_directories_with_metrics(cursor, connection)

        # Update metric-directory associations
        update_metric_directory_associations(cursor, connection)

        # Update directory metric counts
        update_directory_metrics_counts(cursor, connection)

        # Verify consistency
        verify_consistency(cursor)

        # Show report
        show_directory_report(cursor)

        print(f"\n✅ Directory数据更新完成！")
        print(f"完成时间: {datetime.now()}")

        connection.close()

    except Exception as e:
        print(f"❌ 错误: {e}")
        sys.exit(1)

if __name__ == "__main__":
    main()